Element functions

Element function is:

 

·          Function that is always applied to a single item (element) within set.

 

List of functions that are supported in designer:

 

CubePlayer Functions	MDX Functions used to create CubePlayer Functions
Best N	Equivalent to TopCount
Best in Sum	Equivalent to TopSum
Best in Percent	Equivalent to TopPercent
Worst N	Equivalent to BottomCount
Worst in Sum	Equivalent to BottomSun
Worst in Percent	Equivalent to BottomPercent
Order	Equivalent to Order
Order by Alphabet	Combination of Order function and order conditions
Order by Hierarchy	Equivalent to Hierachize
Filter	Equivalent to Filter
Remove member(s)	Combination of Filter (and NOT members …)
First N	Equivalent to Head
Last N	Equivalent to Tail
Without empties	Equivalent to NonEmpty
DrillD Down Level	Implementations of Generate to select level

 

 

           

 

Element functions

Element functions will always be applied to single element.

 

Example                   We have level State Province on Columns

We will apply TopCount 100 on entire axis

 

SELECT

NON EMPTY

TopCount

    (

        {

        [Customer].[hCountry].[State Province].MEMBERS

        }

        ,100, [Measures].[Store Sales]

    )

ON AXIS(0)

FROM

[Sales]

 

At this point Axis function, Dimension functions and element functions are the same since they are

applied on only one dimension.

 

Let us add another element from another dimension/hierarchy

 

SELECT

NON EMPTY

CrossJoin

    (

        {

        TopCount

            (

                {

                [Customer].[hCountry].[State Province].MEMBERS

                }

                ,100, [Measures].[Store Sales]

            )

        },

        {

        [Product].[hProduct].[Product Family].MEMBERS

        }

    )

ON AXIS(0)

FROM

[Sales]

 

At this point Dimension functions and Element functions are the same since they are

only one element from each dimension.

 

Now we will make difference. We will add another element from Customer dimension, USA again

 

SELECT

NON EMPTY

CrossJoin

    (

        {

        TopCount

            (

                {

                [Customer].[hCountry].[State Province].MEMBERS

                }

                ,100, [Measures].[Store Sales]

            ),

            [Customer].[hCountry].[Country].&[USA]

        },

        {

        [Product].[hProduct].[Product Family].MEMBERS

        }

    )

ON AXIS(0)

FROM

[Sales]

 

As you can see Element function remains applied on element from beginning, other words on only

one element.

 

When the Element function is applied to an element, new element will be added to the axis, directly below the element upon which the function is applied to (also called the Initiator). The icon of that element will be, indicating that element as the result of the element function.

The initiator element will be disabled (You can enable it by clicking the check box next to it).

 

The element function TopCount has been applied to the level City. Level City itself has been disabled.

Example: We have level State Province on Columns. We will apply TopCount 100 on the entire axis.

SELECT 
NON EMPTY 
{ 
TopCount 
( 
[Customer].[hCountry].[State Province].AllMembers 
, 100, ([Measures].[Store Sales]) 
) 
} 
ON 0 
FROM 
[Sales]

At this point Axis function, Dimension functions and element functions are the same since they are applied on only one dimension.

Let us add another element from another dimension/hierarchy, Product Family.

SELECT 
NON EMPTY 
CrossJoin 
( 
{ 
TopCount 
( 
[Customer].[hCountry].[State Province].AllMembers 
, 100, ([Measures].[Store Sales]) 
) 
} 
, 
{ 
[Product].[hProduct].[Product Family].AllMembers 
} 
) 
ON 0 
FROM 
[Sales]

At this point Dimension functions and Element functions are the same since they are only one element from each dimension.

Now we will make the difference. We will add another element from Customer dimension, USA.

SELECT 
NON EMPTY 
CrossJoin 
( 
{ 
TopCount 
( 
[Customer].[hCountry].[State Province].AllMembers 
, 100, ([Measures].[Store Sales]) 
) 
,[Customer].[hCountry].[Country].&[USA] 
} 
, 
{ 
[Product].[hProduct].[Product Family].AllMembers 
} 
) 
ON 0 
FROM 
[Sales]

As you can see, the Element function remains applied on element from the beginning, in other words, on only one element.

 

Applying the Element Function

Follow this steps to apply the element function:

1.     Select level or member from the axis

2.     Right-click the element, and select one of the Element functions from the Element Functions submenu

Element Function Best N will be applied to the level Product Family

Dialog will appear.

The Element function dialog

3.     Select Measure to apply the function from drop down list (a default measure will be predefined)

4.     Select other parameters (where applicable)

5.     Click Create

 

New element will be created, and the original element will be disabled.

 

 The new element as the result of the Element function upon the level Product Family

 

You can apply new function to newly created element, as many as you need. You can apply functions to other elements regardless dimension that are coming from. The Designer does not enforce any restrictions regarding the number of functions applied.

 

Whenever you save the designer, you will save functions as well.

 

Editing and removing Element (Inner) functions

 

Editing Element function is not possible due to its nature. However, you can do the following:

1.     Delete the element (the result from the Element function) from the axis

2.     Select the initiator element (you don’t need to enable it)

3.     Create the Element function again

 

Deleting Element function means deleting the resulting element from the list. After deletion, you can enable the initiator element, if needed.